/*
Deployment script for cruxbeforemulticompany
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
IF EXISTS(SELECT name FROM sys.objects WHERE name = N'FK_Users_Companies')
ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_Users_Companies];


GO
IF NOT EXISTS(select name from syscolumns where id=object_id('Roles') and name='IsSystemGenerated')
ALTER TABLE [dbo].[Roles]
    ADD [IsSystemGenerated] BIT CONSTRAINT [DF_Roles_IsSystemGenerated] DEFAULT ((0)) NOT NULL;


GO
IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'UserCompanies')
CREATE TABLE [dbo].[UserCompanies] (
    [UserID]    INT NOT NULL,
    [CompanyID] INT NOT NULL
);



GO
IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'PK_UserCompanies')
ALTER TABLE [dbo].[UserCompanies]
    ADD CONSTRAINT [PK_UserCompanies] PRIMARY KEY CLUSTERED ([UserID] ASC, [CompanyID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'FK_Users_Companies')
ALTER TABLE [dbo].[Users] WITH NOCHECK
    ADD CONSTRAINT [FK_Users_Companies] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'FK_UserCompanies_Companies')
ALTER TABLE [dbo].[UserCompanies] WITH NOCHECK
    ADD CONSTRAINT [FK_UserCompanies_Companies] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'FK_UserCompanies_Users')
ALTER TABLE [dbo].[UserCompanies] WITH NOCHECK
    ADD CONSTRAINT [FK_UserCompanies_Users] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([UserID]) ON DELETE CASCADE ON UPDATE NO ACTION;



GO
IF EXISTS(SELECT name FROM sys.objects WHERE name = N'User_Added_Trigger')
DROP TRIGGER User_Added_Trigger
GO
CREATE TRIGGER User_Added_Trigger 
   ON  Users 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    DECLARE @USERID AS BIGINT
	DECLARE @COMPANYID AS BIGINT
	
	SELECT
		@USERID = UserID,
		@COMPANYID = COMPANYID
	FROM INSERTED
INSERT INTO USERCOMPANIES VALUES(@USERID, @COMPANYID)
END
GO

ALTER TABLE [dbo].[Users] WITH CHECK CHECK CONSTRAINT [FK_Users_Companies]

ALTER TABLE [dbo].[UserCompanies] WITH CHECK CHECK CONSTRAINT [FK_UserCompanies_Companies];

ALTER TABLE [dbo].[UserCompanies] WITH CHECK CHECK CONSTRAINT [FK_UserCompanies_Users];


GO
